﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MJM.RPP.EntidadesNegocio;
using MJM.CBD.Conn;

namespace MJM.RPP.AccesoDatos
{
    public static class ConvenioAD
    {
        public static int Insertar(ConvenioEN obj)
        {
            int codigo = 1;
            SqlConnection conn = new SqlConnection(Cadena.CadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_i_T_MAES_COMCON_Insertar", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_P_inID", obj.IdEmpresa));
                cmd.Parameters.Add(new SqlParameter("@vCOMCON_inTIPCON", obj.IdTipoConvenio));
                cmd.Parameters.Add(new SqlParameter("@vCOMCON_chFECINI", obj.FechaInicio));
                cmd.Parameters.Add(new SqlParameter("@vCOMCON_chFECFIN", obj.FechaFin));
                cmd.Parameters.Add(new SqlParameter("@vCOMCON_chNUMCON", obj.NumeroConvenio));
                SqlParameter prm = new SqlParameter();
                prm.Direction = ParameterDirection.ReturnValue;
                prm.SqlDbType = SqlDbType.Int;
                cmd.Parameters.Add(prm);                
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                codigo = Convert.ToInt32(prm.Value);
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
                codigo = 0;
            }
            return codigo;
        }

        public static List<ConvenioEN> Listar(ConvenioEN obj)
        {
            SqlConnection conn = new SqlConnection(Cadena.CadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_s_T_MAES_COMCON_Listar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            List<ConvenioEN> lst = new List<ConvenioEN>();
            SqlDataReader dr;
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_P_inID", obj.IdEmpresa));
                cmd.Parameters.Add(new SqlParameter("@vCOMCON_inTIPCON", obj.IdTipoConvenio));
                cmd.Parameters.Add(new SqlParameter("@vCOMCON_inFLAELI", obj.Eliminado));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chNOMEMP", obj.NombreComercio));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chRUC", obj.RUC));
                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                string sini, sfin;
                while (dr.Read())
                {
                    var it = new ConvenioEN();
                    it.ID = dr.GetInt32(dr.GetOrdinal("COMCON_P_inID"));
                    it.Eliminado = dr.GetInt32(dr.GetOrdinal("COMCON_inFLAELI"));
                    sini = dr.GetString(dr.GetOrdinal("COMCON_chFECINI"));
                    sfin = dr.GetString(dr.GetOrdinal("COMCON_chFECFIN"));
                    if (sfin.Length == 8)
                        it.FechaFin = sfin.Substring(6, 2) + "/" + sfin.Substring(4, 2) + "/" + sfin.Substring(0, 4);
                    else
                        it.FechaFin = "";
                    if (sini.Length == 8)
                        it.FechaInicio = sini.Substring(6, 2) + "/" + sini.Substring(4, 2) + "/" + sini.Substring(0, 4);
                    else
                        it.FechaInicio = "";

                    it.IdEmpresa = dr.GetInt32(dr.GetOrdinal("EMPCOM_P_inID"));
                    it.IdTipoConvenio = dr.GetInt32(dr.GetOrdinal("COMCON_inTIPCON"));
                    it.NombreComercio = dr.GetString(dr.GetOrdinal("EMPCOM_chNOMEMP"));
                    it.NumeroConvenio = dr.GetString(dr.GetOrdinal("COMCON_chNUMCON"));
                    it.RUC = dr.GetString(dr.GetOrdinal("EMPCOM_chRUC"));
                    it.Rubro = dr.GetString(dr.GetOrdinal("RUBCOM_chDESRUB"));                    
                    lst.Add(it);
                }
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }

        public static int Eliminar(int IdConvenio)
        {
            SqlConnection conn = new SqlConnection(Cadena.CadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_u_T_MAES_COMCON_Eliminar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            int Codigo = 1;
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vCOMCON_P_inID", IdConvenio));
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return Codigo;
        }
    }
}
